USe Karbel4
GO
SET NOCOUNT ON

select identity(int, 1, 1) idx, TABLE_NAME, 0 as level
into #TableLevels
from INFORMATION_SCHEMA.TABLES where TABLE_NAME not in (select ctu.TABLE_NAME 
	from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE ctu
	INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON ctu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
) and TABLE_NAME not in ('sysdiagrams', 'RandomHelper')
ORDER BY TABLE_NAME

select kcu1.TABLE_NAME tbl1, kcu1.COLUMN_NAME col1, kcu2.TABLE_NAME tbl2, kcu2.COLUMN_NAME col2
into #TableRels
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu1
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON rc.CONSTRAINT_NAME = kcu1.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu2 ON rc.UNIQUE_CONSTRAINT_NAME = kcu2.CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLES t ON t.TABLE_NAME = kcu2.TABLE_NAME
where kcu1.TABLE_NAME <> kcu2.TABLE_NAME

/*
select TABLE_NAME, COLUMN_NAME, 'FK' as tip
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where CONSTRAINT_NAME like 'FK%' 
*/

select c.TABLE_NAME tbl1, c.COLUMN_NAME col1, c.DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, ORDINAL_POSITION, tr.tbl2, tr.col2
into #TableCols
from INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN #TableRels tr ON c.TABLE_NAME=tr.tbl1 AND c.COLUMN_NAME=tr.col1
WHERE 
	COLUMNPROPERTY( OBJECT_Id(c.TABLE_NAME), c.COLUMN_NAME, 'IsIdentity') = 0 
	AND COLUMNPROPERTY( OBJECT_Id(c.TABLE_NAME), c.COLUMN_NAME, 'IsComputed') = 0 
	AND DATA_TYPE not in ('varbinary', 'timestamp', 'xml')
ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION


declare @tbl varchar(64), @idx int
select @tbl = ''
while exists(select * from #TableRels)
begin

	select top 1 @tbl = tbl1 from #TableRels where tbl1>@tbl order by tbl1

	if not exists (select * from #TableRels LEFT JOIN #TableLevels ON #TableRels.tbl2=#TableLevels.TABLE_NAME where #TableRels.tbl1 = @tbl AND #TableLevels.TABLE_NAME is null)
	begin
		insert into #TableLevels (TABLE_NAME, level)
		select @tbl, max(level)+1 from #TableLevels where TABLE_NAME in (select tbl2 from #TableRels where tbl1 = @tbl)

		delete from #TableRels where tbl1 = @tbl
	end
	if not exists (select * from #TableRels where tbl1>@tbl) select top 1 @tbl = ''
end


select a.*
into #sonuc
from #TableLevels t 
INNER JOIN #TableCols a ON t.TABLE_NAME = a.tbl1 
ORDER BY t.level, a.tbl1, a.ORDINAL_POSITION

set @idx = 1e3
while exists(select * from #TableLevels where idx<@idx)
begin

	select top 1 @tbl=TABLE_NAME, @idx=idx from #TableLevels where idx<@idx order by idx desc
	exec('delete ' + @tbl)
	exec('DBCC CHECKIdENT (' + @tbl + ', RESEED, 0)')
	
end

--SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' CHECK CONSTRAINT ALL'
--FROM INFORMATION_SCHEMA.TABLES
--WHERE OBJECTPROPERTY(OBJECT_Id(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0 AND TABLE_TYPE = 'BASE TABLE'

declare @ORDINAL_POSITION int, @level int, @sql varchar(MAX), @col1 varchar(32), @DATA_TYPE varchar(32), @tbl2 varchar(32), @col2 varchar(32), @CHARACTER_MAXIMUM_LENGTH int
set @idx = 0
while exists(select * from #TableLevels where idx>@idx)
begin

	select top 1 @tbl=TABLE_NAME, @idx=idx, @level=level from #TableLevels where idx>@idx order by idx

	select @sql = 'INSERT INTO ' + @tbl + ' (%1) select %2'

	select @col1 = '', @ORDINAL_POSITION = 0
	while exists(select * from #sonuc where tbl1=@tbl)
	begin

		select top 1 @col1 = col1, @tbl2=tbl2, @col2=col2, @DATA_TYPE=DATA_TYPE, @CHARACTER_MAXIMUM_LENGTH=CHARACTER_MAXIMUM_LENGTH, @ORDINAL_POSITION=ORDINAL_POSITION from #sonuc where tbl1=@tbl AND ORDINAL_POSITION>@ORDINAL_POSITION order by ORDINAL_POSITION
		select @sql = replace (@sql, '%1', '%1,'+@col1)
 

		select @sql = replace (@sql, '%2', '%2,'+
			case when @col2 is not null then '(select top 1 ' + @col2 + ' from ' + @tbl2 + ' order by NewId())'
			else
				case 
					when @DATA_TYPE = 'varchar' then 'dbo.CreateRandomString(' + convert(varchar, case when @CHARACTER_MAXIMUM_LENGTH=-1 then 50 else @CHARACTER_MAXIMUM_LENGTH end) + ')'
					when @DATA_TYPE = 'char' then 'dbo.CreateRandomString(' + convert(varchar, @CHARACTER_MAXIMUM_LENGTH) + ')'
					when @DATA_TYPE = 'numeric' then '(SELECT convert(int, RAND()*1e8))'
					when @DATA_TYPE = 'int' then '(SELECT convert(int, RAND()*1e6))'
					when @DATA_TYPE = 'smallint' then '(SELECT convert(int, RAND()*3e4))'
					when @DATA_TYPE = 'tinyint' then '(SELECT convert(int, RAND()*128))'
					when @DATA_TYPE = 'bit' then '(SELECT convert(int, RAND()+0.5))'
					when @DATA_TYPE = 'decimal' then '(SELECT convert(int, RAND()*1e8)/100.)'
					when @DATA_TYPE = 'float' then '(SELECT convert(float, RAND()*1e3))'
					when @DATA_TYPE = 'real' then '(SELECT convert(float, RAND()*1e3))'
					when @DATA_TYPE = 'money' then '(SELECT convert(float, RAND()*1e5))'
					when @DATA_TYPE = 'smallmoney' then '(SELECT convert(float, RAND()*1e5))'
					when @DATA_TYPE = 'datetime' then 'DateAdd(second, Rand()*1e6-5e5, GetDate())'
					when @DATA_TYPE = 'smalldatetime' then 'DateAdd(second, Rand()*1e6-5e5, GetDate())'
				end
			end
		)
		delete from #sonuc where tbl1=@tbl AND col1=@col1 

	end
	select @sql = replace (replace (@sql, '%1,', ''), '%2,', '')

	declare @cnt int
	SELECT @cnt = convert(int, RAND()*200+1)
--print @sql
	exec x @sql, @cnt

end


drop table #TableCols
drop table #TableLevels
drop table #TableRels
drop table #sonuc

